<?php

namespace app\service;

use think\facade\Db;
use app\model\SaleModel;
use app\model\SaleIncomeModel;
use app\model\SaleOutlayModel;
use Exception;

class SaleService
{
    public function list($params)
    {
        $where = [];
        $page = $params['page'];
        $pageCount = $params['page_count'];

        //按出发日期查询
        if (!empty($params['start_date']) && !empty($params['end_date'])) {
            if (!empty($params['start_date'])) {
                $where[] = ['a.start_date', '>= time', $params['start_date']];
            }
            if (!empty($params['end_date'])) {
                $where[] = ['a.start_date', '<= time', $params['end_date']];
            }
        }

        $where[] = ['a.type', '=', $params['typeid']];

        if (!empty($params['delivery_no'])) {
            $subQuery = Db::name('sale_outlay')
                ->field('pid')
                ->where('delivery_no', '=', $params['delivery_no'])
                ->where('type', '=', $params['typeid'])
                ->fetchSql(true)->select();
            $where[] = ['a.id', 'in', Db::raw($subQuery)];
        }

        if (!empty($params['product_no'])) {
            $subQuery = Db::name('sale_outlay')
                ->field('pid')
                ->where('product_no', '=', $params['product_no'])
                ->where('type', '=', $params['typeid'])
                ->fetchSql(true)->select();
            $where[] = ['a.id', 'in', Db::raw($subQuery)];
        }

        if (!empty($params['sn'])) {
            $where[] = ['a.sn', 'like', "%{$params['sn']}%"];
        }

        if (!empty($params['client_id'])) {
            $subQuery = Db::name('sale_income')
                ->field('pid')
                ->where('client_id', '=', $params['client_id'])
                ->where('type', '=', $params['typeid'])
                ->fetchSql(true)->select();
            $where[] = ['a.id', 'in', Db::raw($subQuery)];
        }
        if (!empty($params['supplier_id'])) {
            $subQuery = Db::name('sale_outlay')
                ->field('pid')
                ->where('client_id', '=', $params['supplier_id'])
                ->where('type', '=', $params['typeid'])
                ->fetchSql(true)->select();
            $where[] = ['a.id', 'in', Db::raw($subQuery)];
        }
        if (!empty($params['business_type_id'])) {
            $where[] = ['a.business_type_id', '=', $params['business_type_id']];
        }
        if (!empty($params['branch_id'])) {
            $where[] = ['a.branch_id', '=', $params['branch_id']];
        }
        if (!empty($params['category_id'])) {
            $subQuery = Db::name('sale_outlay')
                ->field('pid')
                ->where('category_id', '=', $params['category_id'])
                ->where('type', '=', $params['typeid'])
                ->fetchSql(true)->select();
            $where[] = ['a.id', 'in', Db::raw($subQuery)];
        }
        if (!empty($params['operator_id'])) {
            $where[] = ['a.operator_id', '=', $params['operator_id']];
        }


        $data = SaleModel::alias('a')
            ->field('a.*,b.title as branch,c.title as business_type,g.cn_name as operator')
            ->join('category b', 'a.branch_id=b.id', 'left')
            ->join('category c', 'a.business_type_id=c.id', 'left')
            ->join('user g', 'a.operator_id=g.id', 'left')
            ->where($where)->page($page, $pageCount)->order('id', 'desc')->select();

        foreach ($data as &$v) {
            $monthly = Db::name("monthly")->where("year_month",date("Y-m",strtotime($v['start_date'])))->find();  
            if($monthly){
            $v['is_monthly'] =  1;
            }else{
            $v['is_monthly'] =  0;
            }
            
                
              
            $incomes = Db::name('sale_income')->where(['type' => $v['type'], 'pid' => $v['id']])->select();            
            $total_price = 0;
            $receipt = 0;
            foreach ($incomes as $income) {
                if($income['is_review']){
                    $v['review']=1;
                }
                $re = Db::name('receipt')->where(['type' => $income['type'], 'income_id' => $income['id']])->select();
                foreach ($re as $r) {
                    if($r['is_review']){
                        $v['receipt_review']=1;
                    }
                }
                $v['client'] = Db::name('client')->where('id', $income['client_id'])->value('company_name');
                $total_price += $income['sell_price'] * $income['num'];
                $receipt += Db::name('receipt')->where(['type' => $income['type'], 'income_id' => $income['id']])->sum('money');
            }
            $v['total_price'] = $total_price;
            $v['receipt'] = $receipt;
            $v['not_receipt'] = $total_price - $receipt;

            $outlays = Db::name('sale_outlay')->where(['type' => $v['type'], 'pid' => $v['id']])->select();
            $total_cost_price = 0;
            $payment = 0;
            foreach ($outlays as $outlay) {
                if($outlay['is_review']){
                    $v['review']=1;
                }
                $pa = Db::name('payment')->where(['type' => $outlay['type'], 'outlay_id' => $outlay['id']])->select();
                foreach ($pa as $r) {
                    if($r['is_review']){
                        $v['payment_review']=1;
                    }
                }
                $v['supplier'] = Db::name('supplier')->where('id', $outlay['supplier_id'])->value('supplier_name');
                $total_cost_price += $outlay['cost_price'] * $outlay['num'];
                $payment += Db::name('payment')->where(['type' => $outlay['type'], 'outlay_id' => $outlay['id']])->sum('money');
            }
            $v['total_cost_price'] = $total_cost_price;
            $v['payment'] = $payment;
            $v['not_payment'] = $total_cost_price - $payment;

            $v['profit'] = $total_price - $total_cost_price;
            $v['profit_margin'] = $total_price > 0 ? number_format(($total_price - $total_cost_price) / $total_price * 100, 2) . '%' : '';
        }
        $count = SaleModel::alias('a')->where($where)->count();
        return ["list" => $data, "count" => $count];
    }

    public function info($id)
    {
        $sales = SaleModel::alias('a')
            ->field('a.*,b.title as branch,c.title as business_type,g.cn_name as operator')
            ->join('category b', 'a.branch_id=b.id', 'left')
            ->join('category c', 'a.business_type_id=c.id', 'left')
            ->join('user g', 'a.operator_id=g.id', 'left')
            ->where('id', $id)->find();
        $sales['payments'] = Db::name('payment')->where(['type' => $sales['type'], 'outlay_id' => $sales['id']])->select();
        return $sales;
    }

    

    public function current($type, $id)
    {
        $sales = SaleModel::where(['type' => $type, 'id' => $id])->select();
        foreach ($sales as &$v) {
            $incomes = SaleIncomeModel::where(['type' => $type, 'pid' => $v['id']])->select();
            $v['incomes'] = $incomes;
            foreach ($incomes as &$i) {
                $i['receipts'] = Db::name('receipt')->where(['type' => $i['type'], 'income_id' => $i['id']])->select();
            }
            $outlays = SaleOutlayModel::where(['type' => $type, 'pid' => $v['id']])->select();
            $v['outlays'] = $outlays;
            foreach ($outlays as &$o) {
                $o['payments'] = Db::name('payment')->where(['type' => $o['type'], 'outlay_id' => $o['id']])->select();
            }
        }
        return $sales;
    }

    public function infoBySn($sn)
    {
        $sales = SaleModel::alias('a')
            ->field('a.*,b.title as branch,c.title as business_type,g.cn_name as operator')
            ->join('category b', 'a.branch_id=b.id', 'left')
            ->join('category c', 'a.business_type_id=c.id', 'left')
            ->join('user g', 'a.operator_id=g.id', 'left')
            ->where('sn', $sn)->select();
        return $sales;
    }

    public function save($params)
    {
        if (empty($params['id'])) {
            $data = SaleModel::create($params);
        } else {
            $data = SaleModel::update($params, ['id' => $params['id']]);
        }
        return $data;
    }

    public function delete($params)
    {                
        Db::startTrans();
        try {
            $sale = Db::name('sale')->where('id',$params['id'])->find();
            $income_data = Db::name('sale_income')->where(["pid"=>$sale["id"],"type"=>$sale['type']])->select();
            foreach ($income_data as $i) {
                $receipt_data = Db::name('receipt')->where(["income_id"=>$i["id"],"type"=>$i['type']])->select();
                foreach($receipt_data as $r){
                    Db::name('receipt')->where(["id"=>$r["id"],"type"=>$r['type']])->delete();
                }
                Db::name('receipt')->where(["id"=>$i["id"],"type"=>$i['type']])->delete();
            }
    
            $outlay_data = Db::name('sale_outlay')->where(["pid"=>$sale["id"],"type"=>$sale['type']])->select();   
            foreach ($outlay_data as $o) {
                $payment_data = Db::name('payment')->where(["outlay_id"=>$o["id"],"type"=>$o['type']])->select();
                foreach($payment_data as $p){
                    Db::name('payment')->where(["id"=>$p["id"],"type"=>$p['type']])->delete();
                }
                Db::name('payment')->where(["id"=>$o["id"],"type"=>$o['type']])->delete();
            }   
            SaleModel::destroy($params['id']);
            // 提交事务
            Db::commit();           
        } catch (\Exception $e) {
            // 回滚事务
            Db::rollback();
        }
    }

    public function csv($params)
    {
        $where = [];

        //按出发日期查询
        if (!empty($params['start_date']) && !empty($params['end_date'])) {
            if (!empty($params['start_date'])) {
                $where[] = ['a.start_date', '>= time', $params['start_date']];
            }
            if (!empty($params['end_date'])) {
                $where[] = ['a.start_date', '<= time', $params['end_date']];
            }
        }

        $where[] = ['a.type', '=', $params['typeid']];

        if (!empty($params['delivery_no'])) {
            $subQuery = Db::name('sale_outlay')
                ->field('pid')
                ->where('delivery_no', '=', $params['delivery_no'])
                ->where('type', '=', $params['typeid'])
                ->fetchSql(true)->select();
            $where[] = ['a.id', 'in', Db::raw($subQuery)];
        }

        if (!empty($params['product_no'])) {
            $subQuery = Db::name('sale_outlay')
                ->field('pid')
                ->where('product_no', '=', $params['product_no'])
                ->where('type', '=', $params['typeid'])
                ->fetchSql(true)->select();
            $where[] = ['a.id', 'in', Db::raw($subQuery)];
        }

        if (!empty($params['sn'])) {
            $where[] = ['a.sn', 'like', "%{$params['sn']}%"];
        }

        if (!empty($params['client_id'])) {
            $subQuery = Db::name('sale_income')
                ->field('pid')
                ->where('client_id', '=', $params['client_id'])
                ->where('type', '=', $params['typeid'])
                ->fetchSql(true)->select();
            $where[] = ['a.id', 'in', Db::raw($subQuery)];
        }
        if (!empty($params['supplier_id'])) {
            $subQuery = Db::name('sale_outlay')
                ->field('pid')
                ->where('client_id', '=', $params['supplier_id'])
                ->where('type', '=', $params['typeid'])
                ->fetchSql(true)->select();
            $where[] = ['a.id', 'in', Db::raw($subQuery)];
        }
        if (!empty($params['business_type_id'])) {
            $where[] = ['a.business_type_id', '=', $params['business_type_id']];
        }
        if (!empty($params['branch_id'])) {
            $where[] = ['a.branch_id', '=', $params['branch_id']];
        }
        if (!empty($params['category_id'])) {
            $subQuery = Db::name('sale_outlay')
                ->field('pid')
                ->where('category_id', '=', $params['category_id'])
                ->where('type', '=', $params['typeid'])
                ->fetchSql(true)->select();
            $where[] = ['a.id', 'in', Db::raw($subQuery)];
        }
        if (!empty($params['operator_id'])) {
            $where[] = ['a.operator_id', '=', $params['operator_id']];
        }


        $data = SaleModel::alias('a')
            ->field('a.*,b.title as branch,c.title as business_type,g.cn_name as operator')
            ->join('category b', 'a.branch_id=b.id', 'left')
            ->join('category c', 'a.business_type_id=c.id', 'left')
            ->join('user g', 'a.operator_id=g.id', 'left')
            ->where($where)->order('id', 'desc')->select();

        foreach ($data as &$v) {
            $incomes = Db::name('sale_income')->where(['type' => $v['type'], 'pid' => $v['id']])->select();
            $total_price = 0;
            $receipt = 0;
            foreach ($incomes as $income) {
                $v['client'] = Db::name('client')->where('id', $income['client_id'])->value('company_name');
                $total_price += $income['sell_price'] * $income['num'];
                $receipt += Db::name('receipt')->where(['type' => $income['type'], 'income_id' => $income['id']])->sum('money');
            }
            $v['total_price'] = $total_price;
            $v['receipt'] = $receipt;
            $v['not_receipt'] = $total_price - $receipt;

            $outlays = Db::name('sale_outlay')->where(['type' => $v['type'], 'pid' => $v['id']])->select();
            $total_cost_price = 0;
            $payment = 0;
            foreach ($outlays as $outlay) {
                $v['supplier'] = Db::name('supplier')->where('id', $outlay['supplier_id'])->value('supplier_name');
                $total_cost_price += $outlay['cost_price'] * $outlay['num'];
                $payment += Db::name('payment')->where(['type' => $outlay['type'], 'outlay_id' => $outlay['id']])->sum('money');
            }
            $v['total_cost_price'] = $total_cost_price;
            $v['payment'] = $payment;
            $v['not_payment'] = $total_cost_price - $payment;

            $v['profit'] = $total_price - $total_cost_price;
            $v['profit_margin'] = $total_price > 0 ? number_format(($total_price - $total_cost_price) / $total_price * 100, 2) . '%' : '';
        }

        return $data;
    }
}
